/**
 * PG数据库触发器脚本
 *
 * @author chenw
 * @create 2017-06-09 22:46
 * @email javacspring@gmail.com
 */
-- 创建触发器函数
CREATE OR REPLACE FUNCTION fun_gen_sync_data()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
  jsonStr TEXT;
  hasAttr INTEGER := 0;
BEGIN
  IF (TG_ARGV[0] = '1')
  THEN
    hasAttr := 1;
  ELSE
    hasAttr := 0;
  END IF;
  IF (TG_OP = 'INSERT')
  THEN
    SELECT to_json(NEW)
    INTO jsonStr;
    IF (TG_ARGV[1] = 'H')
    THEN
      INSERT INTO os_dex_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 1, clock_timestamp(), jsonStr, hasAttr);
    ELSEIF (TG_ARGV[1] = 'M')
    THEN
      INSERT INTO os_dex_medium_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 1, clock_timestamp(), jsonStr, hasAttr);
    ELSE
      INSERT INTO os_dex_low_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 1, clock_timestamp(), jsonStr, hasAttr);
    END IF;
    RETURN new;
  ELSEIF (TG_OP = 'UPDATE')
  THEN
    SELECT to_json(NEW)
    INTO jsonStr;
    IF (TG_ARGV[1] = 'H')
    THEN
      INSERT INTO os_dex_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 2, clock_timestamp(), jsonStr, hasAttr);
    ELSEIF (TG_ARGV[1] = 'M')
    THEN
      INSERT INTO os_dex_medium_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 2, clock_timestamp(), jsonStr, hasAttr);
    ELSE
      INSERT INTO os_dex_low_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 2, clock_timestamp(), jsonStr, hasAttr);
    END IF;
    RETURN new;
  ELSEIF (TG_OP = 'DELETE')
  THEN
    SELECT to_json(OLD)
    INTO jsonStr;
    IF (TG_ARGV[1] = 'H')
    THEN
      INSERT INTO os_dex_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 4, clock_timestamp(), jsonStr, hasAttr);
    ELSEIF (TG_ARGV[1] = 'M')
    THEN
      INSERT INTO os_dex_medium_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 4, clock_timestamp(), jsonStr, hasAttr);
    ELSE
      INSERT INTO os_dex_low_sync (tab, act, ctm, data, att)
      VALUES (TG_RELNAME, 4, clock_timestamp(), jsonStr, hasAttr);
    END IF;
    RETURN old;
  END IF;
  RETURN NULL;
END;
$$;

--创建附件表触发器 insert/update
CREATE TRIGGER trg_mod_test_sync_a
BEFORE INSERT OR UPDATE ON test_sync_a
FOR EACH ROW EXECUTE PROCEDURE fun_gen_sync_data(1,'H');

-- 创建附件表触发器 delete
CREATE TRIGGER trg_del_test_sync_a
AFTER DELETE ON test_sync_a
FOR EACH ROW EXECUTE PROCEDURE fun_gen_sync_data(1,'H');

--创建无附件表触发器 insert/update
CREATE TRIGGER trg_mod_test_sync_b
BEFORE INSERT OR UPDATE ON test_sync_b
FOR EACH ROW EXECUTE PROCEDURE fun_gen_sync_data(0,'M');

-- 创建无附件表触发器 delete
CREATE TRIGGER trg_del_test_sync_b
BEFORE DELETE ON test_sync_b
FOR EACH ROW EXECUTE PROCEDURE fun_gen_sync_data(0,'M');